﻿using Dapper;
using HR.IServices.PengIService;
using HR.Models.PengModels;
using System.Data.SqlClient;

namespace HR.Services.PengService
{
    public class JianLiGuanLiServices : IJianLiGuanLiServices
    {

        private readonly IConfiguration conf;
        private readonly string? conStr;

        public JianLiGuanLiServices(IConfiguration conf)
        {
            this.conf = conf;
            conStr = this.conf["ConnectionStrings"];
        }
        public Task<int> Add(JianLiGuanLi jianLiGuanLi)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"insert into engage_resume(human_name, engage_type, human_address, human_postcode, human_major_kind_id, human_major_kind_name, human_major_id, human_major_name, human_telephone, human_homephone, human_mobilephone, human_email, human_hobby, human_specility, human_sex, human_religion, human_party, human_nationality, human_race, human_birthday, human_age, human_educated_degree, human_educated_years, human_educated_major, human_college, human_idcard, human_birthplace, demand_salary_standard, human_history_records, remark,regist_time,interview_status,check_status) values('{jianLiGuanLi.Human_name}','{jianLiGuanLi.Engage_type}','{jianLiGuanLi.Human_address}','{jianLiGuanLi.Human_postcode}','{jianLiGuanLi.Human_major_kind_id}','{jianLiGuanLi.Human_major_kind_name}','{jianLiGuanLi.Human_major_id}','{jianLiGuanLi.Human_major_name}','{jianLiGuanLi.Human_telephone}','{jianLiGuanLi.Human_homephone}','{jianLiGuanLi.Human_mobilephone}','{jianLiGuanLi.Human_email}','{jianLiGuanLi.Human_hobby}','{jianLiGuanLi.Human_specility}','{jianLiGuanLi.Human_sex}','{jianLiGuanLi.Human_religion}','{jianLiGuanLi.Human_party}','{jianLiGuanLi.Human_nationality}','{jianLiGuanLi.Human_race}','{jianLiGuanLi.Human_birthday}','{jianLiGuanLi.Human_age}','{jianLiGuanLi.Human_educated_degree}','{jianLiGuanLi.Human_educated_years}','{jianLiGuanLi.Human_educated_major}','{jianLiGuanLi.Human_college}','{jianLiGuanLi.Human_idcard}','{jianLiGuanLi.Human_birthplace}','{jianLiGuanLi.Demand_salary_standard}','{jianLiGuanLi.Human_history_records}','{jianLiGuanLi.Remark}','{jianLiGuanLi.Regist_time}','{jianLiGuanLi.Interview_status}','{jianLiGuanLi.Check_status}')");
                }
            });
        }

        public Task<int> UpdZt(JianLiGuanLi jianLiGuanLi)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"update engage_resume set check_status='{jianLiGuanLi.Check_status}',pass_checkComment='{jianLiGuanLi.Pass_checkComment}'where res_id = {jianLiGuanLi.Res_id}");
                }
            });
        }

        public Task<List<GongGongShuXing>> AiHao()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='爱好'").ToList();
                }
            });
        }

        public Task<List<GongGongShuXing>> GuoJi()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='国籍'").ToList();
                }
            });
        }

        public Task<List<GongGongShuXing>> JiaoYuNianXian()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='教育年限'").ToList();
                }
            });
        }

        public Task<List<GongGongShuXing>> MinZu()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='民族'").ToList();
                }
            });
        }

        //简历筛选查询全部
        public Task<List<JianLiGuanLi>> SelAllJl()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<JianLiGuanLi>($"select * from engage_resume where recomandation is null").ToList();
                }
            });
        }

        //有效简历查询全部，状态为1
        public Task<List<MsJlLianCha>> SelYouX()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<MsJlLianCha>("select * from [dbo].[engage_resume] where recomandation is not null").ToList();
                }
            });
        }

        public Task<List<GongGongShuXing>> TeChang()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='特长'").ToList();
                }
            });
        }
        //简历 变更  条件查询
        public Task<List<JianLiGuanLi>> TiaoJian(string? type, string? name, string? fourTiaoJian, string? time1, string? time2)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = $@"select * from [dbo].[engage_resume] where recomandation is null and (human_major_kind_name ='{type}' and human_major_name='{name}') and 
                    (human_name like '%{fourTiaoJian}%' or human_mobilephone like'%{fourTiaoJian}%' or human_idcard like'%{fourTiaoJian}%' or human_history_records like'%{fourTiaoJian}%') and 
                    (regist_time between '{time1}' and '{time2}')and interview_status=0";
                    return con.Query<JianLiGuanLi>(sql).ToList();
                }
            });
        }

        public Task<int> UpdateTwo(JianLiGuanLi jianLiGuanLi)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    //未面试的状态为1
                    return con.Execute($"update engage_resume set recomandation='{jianLiGuanLi.Recomandation}',interview_status=1  where res_id='{jianLiGuanLi.Res_id}'");
                }
            });
        }

        public Task<List<GongGongShuXing>> XueLi()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='学历'").ToList();
                }
            });
        }

        //有效简历条件查询
        public Task<List<JianLiGuanLi>> YouXiao(string? type, string? name, string? fourTiaoJian, string? time1, string? time2)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = @$"select * from engage_resume where recomandation is not null and (human_major_kind_name ='{type}' and human_major_name='{name}') and (human_name like '%{fourTiaoJian}%' or human_mobilephone like'%{fourTiaoJian}%' or human_idcard like'%{fourTiaoJian}%' or human_history_records like'%{fourTiaoJian}%') and regist_time between '{time1}' and '{time2}'";
                    return con.Query<JianLiGuanLi>(sql).ToList();
                }
            });
        }

        public Task<List<GongGongShuXing>> ZhengZhiMianMao()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='政治面貌'").ToList();
                }
            });
        }

        public Task<List<GongGongShuXing>> ZhuanYe()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='专业'").ToList();
                }
            });
        }

        public Task<List<GongGongShuXing>> ZongJiao()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='宗教信仰'").ToList();
                }
            });
        }

        public Task<List<JianLiGuanLi>> SelLuYonJL()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = $"select * from engage_resume where check_status = 1";
                    return con.Query<JianLiGuanLi>(sql).ToList();
                }
            });
        }

        //修改check状态为2，录用审批要查询
        public Task<int> UpdCheckstu(JianLiGuanLi jianLiGuanLi)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"update engage_resume set check_status = {jianLiGuanLi.Check_status} where res_id = {jianLiGuanLi.Res_id}");
                }
            });
        }

        public Task<List<MsJlLianCha>> msJlLians()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = $"select * from view_engage_er where check_status = 3";
                    return con.Query<MsJlLianCha>(sql).ToList();
                }
            });
        }

        public Task<int> UpdCheckstus(JianLiGuanLi jianLiGuanLi)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"update engage_resume set check_status = {jianLiGuanLi.Check_status} where res_id = {jianLiGuanLi.Res_id}");
                }
            });
        }

        public Task<List<MsJlLianCha>> msJlLianss()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = $"select * from view_engage_er where check_status = 4";
                    List<MsJlLianCha> list = con.Query<MsJlLianCha>(sql).ToList();
                    return list;
                }
            });
        }

        public Task<List<GongGongShuXing>> ZhiCheng()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<GongGongShuXing>($"select * from config_public_char where attribute_kind='职称'").ToList();
                }
            });
        }
        //面试表修改第二次面试的字段
        public Task<int> UPDMianShics(MianShi ms)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"update engage_interview set interview_amount = {ms.interview_amount}, image_degree = '{ms.image_degree}',native_language_degree = '{ms.native_language_degree}',foreign_language_degree = '{ms.foreign_language_degree}',response_speed_degree  = '{ms.response_speed_degree}',EQ_degree = '{ms.EQ_degree}',IQ_degree = '{ms.IQ_degree}',multi_quality_degree = '{ms.multi_quality_degree}',registe_time = '{ms.registe_time}',interview_comment='{ms.interview_comment}'where ein_id = {ms.ein_id}");
                }
            });
        }
    }
}
